The present invention relates to database management systems, and more particularly, to database management systems in which selectable system events trigger programmable actions upon satisfaction of programmable conditions.
Databases are organized arrangements of information on computer systems convenient for storing and retrieving information. Database management systems provide resources for conveniently creating, maintaining, deleting and protecting access to databases. In a relational database system a single database is made up of one or more related tables, each table having information arranged in rows and columns. Each column represents a kind of information and each row stores values for all the columns in the table.
Users interact with a database using a query language such as the Structure Query Language (SQL). SQL is a powerful non-procedural language which serves as the industry standard for relational database management systems. Data Manipulation Language (DML) statements are SQL statements that retrieve or manipulate data in tables. DML statements are the most frequently used SQL statements. Data Definition Language (DDL) statements are SQL statements that define, alter the structure of, and drop tables.
In a database management system, a trigger is an object that specifies a series of actions to be automatically performed when a specific event occurs. According to industry standards, DML statements are the events that cause user-defined triggers to be activated (or xe2x80x9cfiredxe2x80x9d). For example, in a relational database, user-defined triggers may be designed to fire when a row of a database table or a table view is updated, inserted, or deleted. Accordingly, each user-defined trigger is typically associated with a single database table. That is, in a conventional database management system, the scope of the user-defined trigger is the table level of the database.
The series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, Calif.). In conformance with industry standards, these instructions must be able to access the data values of table columns corresponding to an affected row before the triggering DML statement was applied (the xe2x80x9cold valuesxe2x80x9d) and after the modification was applied (the xe2x80x9cnew valuesxe2x80x9d).
Since triggers are objects, database customers can define, remove, and store triggers associated with a database table, and the database management system keeps track of which triggers have been defined for which table by storing that information as metadata (information about data) associated with the table in a data dictionary for the database. Consequently, triggers enable database customers to implement additional functionality in their databases for such purposes as enforcement of business rules and security.
Conventional trigger mechanisms can be wasteful of system resources. Specifically, triggers often include conditions. When a trigger is fired, the condition associated with the trigger is evaluated. If the condition is satisfied, then some specified action is performed. If the condition is not satisfied, then no action is performed. Thus, the number of times a trigger fires may be magnitudes greater than the number of times the action associated with the trigger actually has to be performed.
When the triggering event has occurred, substantial system resources are required to evaluate the condition. For example, to evaluate any allowed SQL condition, code encompassing about 20,000 instructions must be executed. This consumption of resources is exacerbated as the number of triggers associated with each event increases. For example, if a database has 1000 users, and each has defined 3 triggers for a xe2x80x9cdeletexe2x80x9d operation on the same table, then 3000 triggers fire when a delete event occurs on that table, and 3000 conditions have to be evaluated, each requiring the execution of code with 20,000 instructions. This evaluation load is the same whether or not the conditions are satisfied. Thus it is possible to consume substantial resources just to conclude that few or no actions are to be executed.
Trigger conditions and actions are procedures typically stored with table metadata in a data dictionary within the database. Trigger events are confined to table and view level DML operations insert, update, delete. When a user defines a trigger, the user specifies the event name. The event name must be chosen from a short list of selectable events. The selectable events are INSERT, UPDATE and DELETE. The user must then specify the table name, e.g., EMPLOYEE if the employee table is to have the trigger. Then the user provides the condition as an SQL expression and the action as a stored procedure.
The trigger is then fired when the user executes an SQL statement that performs the selected DML operation on the specified table. When the table is referenced in an SQL statement as the result of a database tool or a user application process run at a later time by the same or a different user, the table metadata is loaded into memory, including a map of all the table triggers and events on which they fire. The trigger events are compared to the SQL statement to see if the trigger is applicable, i.e., if the SQL statement includes the DML operation that is the event for the trigger, e.g. the trigger is for INSERT and the SQL statement includes the INSERT operation for this table. If the trigger is applicable, then the condition and action statements are loaded into a cache in dynamic memory from disk, if not already there, compiled if necessary, and combined with the rest of the compiled SQL statement. Then when the compiled SQL statement is executed, the trigger condition is evaluated using the available old and/or new values on the row. If the condition is satisfied, the action is executed, again using the old or new values if needed.
There are many database system states and system operations for which the conventional system does not allow customers to define triggers. These states and operations can be important to the customer but have a scope beyond that of the table level. For example, a user of several databases may wish to respond to a particular database starting up. As another example, the owner of a table in a database may want to respond to a particular user logging on. As further examples, the user may wish to respond to the amount of available volatile storage, to the occurrence of a particular DDL statement, to the occurrence of an error condition, or to the state of a queue of DML operations. With the conventional database management system, the customer can not define triggering events based on such states and operations beyond the scope of the table level.
One technique for customers to define actions that are performed upon the occurrence of such important situations involves polling the database by periodically sending requests from a database application to the database server that manages the database. In polling, the state of the database (as recorded, for example, in dynamic performance tables) is provided to the database application in response to a specific request for that information. The information thus provided to the database application may then be used by the database application to determine whether to issue certain commands to the database server.
With polling, the request is repeated at regular intervals of time dependent upon a polling frequency. If the polling frequency is very high, the polling will consume many system resources and interfere with the performance of the database being tracked. If the polling frequency is very low, important events may be discovered too late, or missed altogether. Thus polling can be expensive, unreliable, or both.
There is a need to reduce the consumption of system resources in applying triggers fired at the table level upon DML operations. There is also a need for users to respond to system states and operations beyond the scope of the table level, without resorting to polling.
Techniques are provided for database management using triggers of different scopes. According to one technique, data are received that indicate a selected scope for a trigger. The selected scope for the trigger is selected from several selectable scopes of database management. Each scope is associated with a set of one or more events. Data are also received that indicate a selected event that belongs to the set of one or more events. Trigger metadata for the trigger are stored, including data identifying the selected scope and the selected event.
In another aspect of the invention, a technique for executing a process in a database management system includes maintaining metadata about a particular scope. The particular scope is associated with a set of one or more events. The metadata about the particular scope includes one or more triggers associated with the particular scope. When the process causes a new event in the set of one or more events to occur, the technique checks flags that indicate which events in the set are associated with at least one trigger. If the flags indicate that the new event is associated with a trigger, then the trigger is fired.
In another aspect of the invention, a technique for executing a process in a database management system includes maintaining metadata about multiple scopes, each scope associated with a set of one or more events. A hierarchical relationship exists among the scopes. The metadata about the scopes includes a hierarchy indicating the hierarchical relationship. The technique also maintains metadata about each particular scope. The metadata about each particular scope includes the set of one or more events associated with the particular scope, one or more triggers associated with the particular scope, and flags that indicate which events in the set of one or more events associated with the particular scope are associated with at least one trigger. The process is associated with a first scope. The flags for the first scope are loaded into a private cache for the process. Also loaded into the private cache are the flags for a second scope. The second scope occurs at a higher level in the hierarchy than the level of the first scope.
In another aspect of the invention, a trigger map for a certain scope is stored on a computer-readable medium and used for database management. The trigger map includes flags that correspond to selectable events for the scope. A flag is xe2x80x9conxe2x80x9d if a trigger is defined for that event at that scope.
In another aspect of the invention, a computer-readable medium bears information used for database management. This information includes a field indicating the number of triggers in a cache and at least one trigger specification area. The trigger specification area holds data which indicates one of several selectable events for firing the trigger.